package mindteck.store;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class ProductDAO extends BaseDAO{
	
	private final String GETPRODUCTDETAILBYID = "SELECT * FROM PRODUCT WHERE PRODUCTID = ?";
	private final String UPDATEQUANTITYINDB = "UPDATE PRODUCT SET QUANTITYLEFT = ? WHERE PRODUCTID = ?";
	
	public ProductDAO(){
		
	}
	
	public Product getProductDetailById(int productId){
		Connection connection = null;
		PreparedStatement pst = null;
		Product product = null;
		
		try{
			connection = getPoolOfConnections();
			pst = connection.prepareStatement(GETPRODUCTDETAILBYID);
			pst.setInt(1, productId);
			
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				product = new Product();
				
				product.setProductId(productId);
				product.setDescription(rs.getString("description"));
				product.setProductName(rs.getString("name"));
				product.setPrice(rs.getDouble("unitprice"));
			}
		
			
		}catch(SQLException ep){
			ep.printStackTrace();
	
		}finally{
			if(connection != null){
				try {
					connection.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return product;
		
		
	}
	
	public boolean updateProductQuantityInDB(int newQ, Product product){
		boolean t = true;
		Connection connection = null;
		PreparedStatement ps = null;
		try{
			connection = getPoolOfConnections();
			ps = connection.prepareStatement(UPDATEQUANTITYINDB);
			
			ps.setInt(1, newQ);
			ps.setInt(2, product.getProductId());
			
			ps.executeQuery();
			
		}catch(SQLException xp){
			xp.printStackTrace();
			t = false;
		}finally{
			if(connection != null){
				try {
					connection.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		
		
		
		return t;
	}
	
	/*if -999999999 is returned, there is an error in 
	 * productname and productid pair*/
	public int getQuantityLeftInDB(Product prod){
		String prodName = prod.getProductName();
		int prodId = prod.getProductId();
		Connection conn = null;
		PreparedStatement pp = null;
		int temp=-999999999;
		
		try{
			conn = getPoolOfConnections();
			/*to assure we get the correct value, write a query involving productid our PK*/
			pp = conn.prepareStatement("SELECT * FROM PRODUCT WHERE PRODUCTNAME = ? " +"AND " + 
			"PRODUCTID = " +prodId);
			pp.setString(1, prodName);
			ResultSet rs = pp.executeQuery();
			
			if(rs.next()){
				temp = rs.getInt("QUANTITYLEFT");
			}
			
		}catch(SQLException exp){
			exp.printStackTrace();
		}finally{
			if(conn != null){
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		
		return temp;
	}
	

}
